package dao.impl;

import dao.QuestionDao;
import domain.Question;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import utils.JDBCUtils;
import utils.LyyLogUtil;

import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.List;

public class QuestionDaoImpl implements QuestionDao {
    private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public boolean addQuestion(Question question) throws Exception{
        String sql0="SELECT COUNT(*) FROM question WHERE TO_DAYS(proposeTime)=TO_DAYS(NOW()) AND userId=?;";
        Integer count = template.queryForObject(sql0, Integer.class, question.getUserId());
        LyyLogUtil.logD("sql0:"+sql0);
        LyyLogUtil.logD("该用户当天反馈了"+count+"条建议");
        if(count>=100){//每天最多反馈100次
            throw new Exception("您当天的反馈次数已用完！");
        }
        String sql="insert into question values(?,?,?,?,?,?,?)";
        int update=0;
        try {
            update = template.update(sql, null, question.getUserId(), question.getContent(), question.getTel(), question.getAnswer(),
                    question.getProposeTime(), question.getAnswerTime());
        } catch (Exception e) {
            LyyLogUtil.logeE(e.getMessage());
        }
        return update!=0;
    }

    @Override
    public List<Question> queryByPage(HttpServletRequest request) throws Exception {
        List<Object> paraList=new ArrayList<>();
        String userId = request.getParameter("userId");
        String currPage = request.getParameter("currPage");
        String pageSize = request.getParameter("pageSize");
        String orderFlag = request.getParameter("orderFlag");//标志排序方式
        String answerFlag = request.getParameter("answerFlag");//标志是否已答复
        String startTime = request.getParameter("startTime");//起始时间
        String endTime = request.getParameter("endTime");//结束时间
        StringBuilder stringBuilder=new StringBuilder("select * from question where 1=1 ");
        if (currPage==null){
            throw new Exception("当前页码不能为空！");
        }else if(pageSize==null){
            throw new Exception("每页大小不能为空！");
        }else if(orderFlag==null){
            throw new Exception("排序方式不能为空！");
        }else if (startTime==null){
            throw new Exception("起始时间不能为空！");
        }else if(endTime==null){
            throw new Exception("结束时间不能为空！");
        }
        if(userId!=null){
            stringBuilder.append("and userId=? ");
            paraList.add(userId);
        }
        if("1".equals(answerFlag)){//已答复
            stringBuilder.append("and answer is not null ");
        }else if("0".equals(answerFlag)){//未答复
            stringBuilder.append("and answer is null ");
        }
        stringBuilder.append("and proposeTime>? and proposeTime<? ");
        paraList.add(startTime);
        paraList.add(endTime);

        if("0".equals(orderFlag)){//由近及远排序
            stringBuilder.append("order by proposeTime desc ");
        }else if("1".equals(orderFlag)){
            stringBuilder.append("order by proposeTime asc ");
        }

        stringBuilder.append("limit ?,?");
        int currPageN = Integer.parseInt(currPage);
        int pageSizeN = Integer.parseInt(pageSize);
        paraList.add((currPageN-1)*pageSizeN);
        paraList.add(pageSizeN);
        List<Question> list;
        try {
            LyyLogUtil.logD("查询问题表的sql为："+stringBuilder.toString());
            list = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<Question>(Question.class),paraList.toArray());
        } catch (Exception e) {
            LyyLogUtil.logD(e.getMessage());
            throw e;
        }
        return list;
    }

    @Override
    public int queryTotalCount(HttpServletRequest request) throws Exception{
        int count=0;
        List<Object> paraList=new ArrayList<>();
        String userId = request.getParameter("userId");
        String currPage = request.getParameter("currPage");
        String pageSize = request.getParameter("pageSize");
        String orderFlag = request.getParameter("orderFlag");//标志排序方式
        String answerFlag = request.getParameter("answerFlag");//标志是否已答复
        String startTime = request.getParameter("startTime");//起始时间
        String endTime = request.getParameter("endTime");//结束时间
        StringBuilder stringBuilder=new StringBuilder("select count(*) from question where 1=1 ");
        if (currPage==null){
            throw new Exception("当前页码不能为空！");
        }else if(pageSize==null){
            throw new Exception("每页大小不能为空！");
        }else if(orderFlag==null){
            throw new Exception("排序方式不能为空！");
        }else if (startTime==null){
            throw new Exception("起始时间不能为空！");
        }else if(endTime==null){
            throw new Exception("结束时间不能为空！");
        }
        if(userId!=null){
            stringBuilder.append("and userId=? ");
            paraList.add(userId);
        }
        if("1".equals(answerFlag)){//已答复
            stringBuilder.append("and answer is not null ");
        }else if("0".equals(answerFlag)){//未答复
            stringBuilder.append("and answer is null ");
        }
        stringBuilder.append("and proposeTime>? and proposeTime<? ");
        paraList.add(startTime);
        paraList.add(endTime);

        if("0".equals(orderFlag)){//由近及远排序
            stringBuilder.append("order by proposeTime desc ");
        }else if("1".equals(orderFlag)){
            stringBuilder.append("order by proposeTime asc ");
        }

        try {
            LyyLogUtil.logD("查询问题表的sql为："+stringBuilder.toString());
            count=template.queryForObject(stringBuilder.toString(),Integer.class,paraList.toArray());
        } catch (Exception e) {
            LyyLogUtil.logD(e.getMessage());
            throw e;
        }
        return count;
    }

    @Override
    public boolean deleteQuestion(int questionId) throws Exception {
        String sql="delete from question where id=?";
        int update = 0;
        try {
            update = template.update(sql, questionId);
        } catch (Exception e) {
            LyyLogUtil.logeE(e.getMessage());
            throw e;
        }
        return update!=0;
    }
}
